package cz.acies.uniql.sql;

import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.dom4j.Element;
import org.dom4j.dom.DOMElement;



import cz.acies.uniql.dialect.Dialect;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.TableMetaData;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLColumn;
import cz.acies.uniql.sql.element.UniQLFrom;
import cz.acies.uniql.sql.element.UniQLValue;
import cz.acies.utils.StringUtils;

// TODO: Auto-generated Javadoc
/**
 * The Class InsertQuery.
 */
public class InsertQuery extends Query
{ 
	private static final long serialVersionUID = -666461305275147462L;

	/** The tables. */
	private ArrayList<UniQLExpr> tables;
	
	/** The keys. */
	private ArrayList<Object> keys;
	
	/** The values. */
	private ArrayList<Object> values;
	
	/** The params. */
	private Map<Object, Object> params;
	
	/**
	 * Konstruktor tridy insert query.
	 */
	public InsertQuery() {
	}

	public InsertQuery(DBMetaData dbmd) {
		super(dbmd.getDialect(), dbmd);
	}

	/**
	 * Konstruktor tridy insert query.
	 * 
	 * @param dialect - dialect
	 * @param dbmd - dbmd
	 */
	public InsertQuery(Dialect dialect, DBMetaData dbmd) {
		super(dialect, dbmd);
	}

	public InsertQuery(InsertQuery query) throws Exception {
		this();
		InsertQuery clone = (InsertQuery)query.clone();
		
		setId(clone.getId());
		setName(clone.getName());
		setDialect(clone.getDialect());
		setDBMetaData(clone.dbmd());
		setAliasMap(clone.getAliasMap());
		
		this.tables = clone.getTables();
		this.keys = clone.getKeys();
		this.values = clone.getValues();
		this.params = clone.getParams();
	}

	// -----  -----------------------------------------------------------------
	
	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.Query#toSQLString()
	 */
	public String toSQLString() throws Exception {
		return this.translate();
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.Query#translate()
	 */
	public String translate() throws Exception {
		Dialect dialect = this.getDialect();
		String tableName = "";
		//String schema = dialect.getSchema();
		
		StringBuffer sb = new StringBuffer("INSERT INTO ");		
		if (this.getTables() != null) {
			for (Iterator<UniQLExpr> i = getTables().iterator(); i.hasNext();) {
				UniQLExpr table = i.next();
				table.setUniQLProperties(this);
				tableName = table.getName();
				if (getDialect().isKeyword(tableName/*.toUpperCase()*/)) {
					tableName = getDialect().getKeywordAlter(tableName/*.toUpperCase()*/);
				}
				sb.append(table.translate());
				if (i.hasNext()) sb.append(", ");
	        }
		}
		Map<Integer, UniQLExpr> map = new HashMap<Integer, UniQLExpr>(); 
		if (keys != null && keys.size() > 0) {
			sb.append(" (");
			int j = 0;
			for (Iterator<Object> i = keys.iterator(); i.hasNext();) {
				Object obj = i.next();
				if (obj instanceof String) { 
					UniQLColumn expr = new UniQLColumn(dialect);
					expr.setUniQLProperties(this);
					expr.setName((String)obj);
					sb.append(expr.translate());
					map.put(new Integer(j), expr);
				}
				if (i.hasNext()) sb.append(", ");
				j++;
			}
			sb.append(")");
		}
		if (values != null && values.size() > 0) {
			TableMetaData tbmd = this.dbmd().getTable(tableName);
			sb.append(" VALUES ");
			sb.append("(");
			int j = 0;
			
			for (Iterator<Object> i = values.iterator(); i.hasNext();) {
				Object obj = i.next();
				if (obj instanceof UniQLExpr) {
					((UniQLExpr)obj).setUniQLProperties(this);
					sb.append(((UniQLExpr)obj).translate());
				} else {
					String value = null; //obj.toString();
					FieldMetaData fmd = null;
					String name = null;
					if (map != null && map.size() > 0) {
						name = ((UniQLColumn)map.get(new Integer(j))).getName();
						try {
							fmd = (FieldMetaData)tbmd.getFieldByName(name);	
						} catch (Exception exc) {
							System.out.println("-----" + "InsertQuery.translate( ERROR ) -----\n" +
								"table("+j+") = "+tableName+"\n" +
								"name = ."+name+".\n" +
								"tbmd.getColNameIndex = "+tbmd.getColNameIndex()+"\n" +
								"field = "+tbmd.getFields()+"\n" +
								"-----   END   -----"
							);
							exc.printStackTrace();
						}
					} else {
						fmd = (FieldMetaData)tbmd.getFields().get(j);
					}
					if (fmd == null) { 
						System.out.println("----- InsertQuery.translate( ERROR ) ----- [fmd = null]\n\t" +
							"column = " + name + "\n\t" +
							"pos = " + j + "\n\t" +
							"table = " + getTables() + "\n\t" +
							"keys = " + map + "\n" +
							"-----   END   -----"
						);
					}
					if (obj != null) {
						value = obj.toString();	
					}
					boolean nullable = fmd.isNullable();
					String defVal = fmd.getDefaultValue();
					int type = fmd.getSqlType();
					int length = fmd.getSize();
					
					if (value != null) {
						sb.append(convertValueByType(dialect, type, value, length));
					} else {
						if (nullable) {
							sb.append("NULL");							
						} else {
							if (defVal == null) {
								value = getBlankValue(type).toString();
							} else {
								value = defVal;
							}
							sb.append(convertValueByType(dialect, type, value, length));
						}
					}
				}
				if (i.hasNext()) sb.append(", ");
				j++;
	        }			
			sb.append(")");	
		}
		return sb.toString();
	}

	private Object getBlankValue(int type) {
		Object value = "";
		switch (type) {
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
			case Types.BLOB: {
				value = "";
				break;
			}
			case Types.BIT:
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.NUMERIC:
			case Types.INTEGER:
			case Types.BIGINT: {
				value = new Integer(0);
				break;
			}
			case Types.DECIMAL:
			case Types.REAL:
			case Types.FLOAT:
			case Types.DOUBLE: {
				value = new Double(0.00);
				break;
			}
			case Types.DATE: {
				value = java.sql.Date.valueOf("1900-01-01");
				break;
			}
			case Types.TIMESTAMP: {
				value = Timestamp.valueOf("1900-01-01 00:00:01");
				break;
			}
		}
		return value;
	}

	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#clear()
	 */
	public InsertQuery clear() {
		if (tables != null) tables.clear();	tables = null;
		if (keys != null) keys.clear();		keys = null;
		if (values != null) values.clear();	values = null;
		if (params != null) params.clear();	params = null;
		
		return this;
	}
	
	// -----  -----------------------------------------------------------------
	
	/**
	 * Prida param.
	 * 
	 * @param key - key
	 * @param value - value
	 * @return vraci insert query
	 */
	public InsertQuery addParam(String key, String value) {
		if (this.keys == null) this.keys = new ArrayList<Object>();
		if (this.values == null) this.values = new ArrayList<Object>();
		if (this.keys.contains(key)) {
			int index = this.keys.indexOf(key);
			if (index > -1) this.values.set(index, value);
		} else {
			this.keys.add(key.trim());
			this.values.add(value);
		}
		return this;	
	}

	/**
	 * Metoda p��d� k sloupci tabulky (parametr key) z�pisovou hodnotu (pramatr value), pokud je�t� eexistuje,
	 * pokud existuje, p�ep�e z�pisovou hodnotu, hodnotou paramtru value, u polo�ky key.
	 * 
	 * @param value - value
	 * @return vraci insert query
	 * @return
	 */
	public InsertQuery addParam(String value) {
		if (this.values == null) this.values = new ArrayList<Object>();
		this.values.add(value);
		return this;
	}

	/**
	 * Metoda p��d� k sloupci tabulky (parametr key) z�pisovou hodnotu (pramatr value), pokud je�t� eexistuje,
	 * pokud existuje, p�ep�e z�pisovou hodnotu, hodnotou paramtru value, u polo�ky key.
	 * 
	 * @param key - key
	 * @param value - value
	 * @return vraci insert query
	 * @return
	 */
	public InsertQuery addParam(String key, UniQLExpr value) {
		if (this.keys == null) this.keys = new ArrayList<Object>();
		if (this.values == null) this.values = new ArrayList<Object>();
		//key = (!getDialect().isKeyword(key)) ? key : getDialect().getKeywordAlter(key);
		value.setDialect(this.getDialect());
		if (this.keys.contains(key)) {
			//System.out.println("InsertQuery.addParam(2) "+key+", "+value);
			int index = this.keys.indexOf(key);
			if (index > -1) this.values.set(index, value);
		} else {
			//System.out.println("InsertQuery.addParam(3) "+key+", "+value);
			this.keys.add(key.trim());
			this.values.add(value);
		}
		return this;	
	}
	
	/**
	 * Prida param.
	 * 
	 * @param value - value
	 * @return vraci insert query
	 */
	public InsertQuery addParam(UniQLExpr value) {
		if (this.values == null) this.values = new ArrayList<Object>();
		value.setDialect(this.getDialect());
		this.values.add(value);
		return this;
	}

	
	/**
	 * Prida sadu parametr� zadan�ch v parametrech akey(pole String[]) a 
	 * avalue (pole Object[]). 
	 * 
	 * @param akey - akey pole String[]
	 * @param avalue - avalue pole Object[] m�e obsahovat pouze objekty typu String 
	 * nebo UniQLExpr
	 * @return vraci objekt InsertQuery
	 * @throws Exception the exception
	 */
	public InsertQuery addParams(String[] akey, Object[] avalue) throws Exception {
		//boolean akeyEmpty = (akey == null || akey.length > 0);
		boolean avalueEmpty = (avalue == null || avalue.length == 0);
		if (avalueEmpty) return this;
		
		if (this.values == null) this.values = new ArrayList<Object>();
		if (this.keys == null) 	this.keys = new ArrayList<Object>();
				
		for (int i = 0; i < avalue.length; i++) {
			String key = null;
			UniQLExpr value = null;			
			if (i < akey.length) {
				key = akey[i];
			}
			Object obj = avalue[i];
			if (obj != null) {
				value = this.makeExpr(obj);
			}
			if (key != null && value != null) {
				key = key.trim();
				value.setDialect(this.getDialect());
				if (this.keys.contains(key)) {
					int index = this.keys.indexOf(key);
					if (index > -1) this.values.set(index, value);
				} else {
					this.keys.add(key);
					this.values.add(value);
				}
			}
		}
		return this;
	}
	
	/**
	 * Prida sadu parametrů zadaných v parametrech akey(pole String[]) a 
	 * avalue (pole Object[]). 
	 * 
	 * @param akey - akey pole String[]
	 * @param avalue - avalue pole Object[] může obsahovat pouze objekty typu String 
	 * nebo UniQLExpr
	 * @return vraci objekt InsertQuery
	 * @throws Exception the exception
	 */
	public InsertQuery addParams(Map<String, Object> params) throws Exception {
		String[] akey =  new String[] {}; 
		akey =  new ArrayList<String>(params.keySet()).toArray(akey);
		Object[] avalue = new ArrayList<Object>(params.values()).toArray();
		return this.addParams(akey, avalue);
	}

	/**
	 * Make expr.
	 * 
	 * @param obj - obj
	 * @return vraci uni ql expr
	 */
	private UniQLExpr makeExpr(Object obj) {
		if (obj instanceof String) {
			String s = ((String)obj).trim();
			UniQLExpr expr = null;
			if (s.matches(StringUtils.NUMERIC_PATTERN) ||
				s.matches(StringUtils.INTEGER_PATTERN) ||
				s.matches("\\'.*\\'")) {
					
				expr = new UniQLValue(obj.toString());						
			} else if (s.matches("\\w*?\\.?\\w+")) {
				expr = new UniQLColumn(obj.toString());	
			} else {
				expr = new UniQLValue(obj.toString());						
			}
			expr.setDialect(getDialect());
			expr.setAliasMap(getAliasMap());
			return expr; 

		} else if (obj instanceof UniQLExpr) {
			((UniQLExpr)obj).setDialect(getDialect());
			((UniQLExpr)obj).setAliasMap(getAliasMap());
			return ((UniQLExpr)obj);
		}
		return null;
	}

	/**
	 * Navraci hodnotu param index.
	 * 
	 * @param key - key
	 * @return - vraci hodnotu param index
	 */
	/*
	private int getParamIndex(String key) {
		if (keys != null && keys.size() > 0) {
			for (int i = 0; i < keys.size(); i++) {
				UniQLColumn expr = (UniQLColumn)keys.get(i);
				String name = expr.getName();
				if (name.equals(key)) return i;
			}
		}
		return -1;
	}
	*/
	/**
	 * Metoda p�ep�e z�pisovou hodnotu, hodnotou paramtru value, u polo�ky key.
	 * 
	 * @param key - key
	 * @param value - value
	 * @return vraci insert query
	 * @return
	 */
	public InsertQuery replaceParam(String key, String value) {
		return this.addParam(key, value); 	
	}
	
	/**
	 * Metoda p�ep�e z�pisovou hodnotu, hodnotou paramtru value, u polo�ky key.
	 * 
	 * @param key - key
	 * @param value - value
	 * @return vraci insert query
	 * @return
	 */
	public InsertQuery replaceParam(String key, UniQLExpr value) {
		return this.addParam(key, value); 
	}
	
	// ----- Tables -----------------------------------------------------------
	
	/**
	 * Prida from.
	 * 
	 * @param tableName - table name
	 * @return vraci insert query
	 */
	public InsertQuery addFrom(String tableName) {
		/*if (getDialect().isKeyword(tableName.toUpperCase())) {
			tableName = getDialect().getKeywordAlter(tableName.toUpperCase());
		}*/
		UniQLFrom table = new UniQLFrom(tableName);
		table.setDialect(this.getDialect());		
		getTables().add(table);
		return this;
	}

	/**
	 * Prida from.
	 * 
	 * @param tableName - table name
	 * @param alias - alias
	 * @return vraci insert query
	 */
	public InsertQuery addFrom(String tableName, String alias) {
		UniQLFrom table = new UniQLFrom(tableName, alias);
		table.setDialect(this.getDialect());
		getTables().add(table);
		return this;
	}
	
	/**
	 * Prida from.
	 * 
	 * @param from - from
	 * @return vraci insert query
	 */
	public InsertQuery addFrom(UniQLFrom from) {
		from.setDialect(this.getDialect());
		getTables().add(from);
		return this;
	}
	
	/**
	 * Metoda je ekvivalet metody addFrom(String tableName).
	 * 
	 * @param tableName - table name
	 * @return vraci ukazatel na instanci InsertQuery
	 */
	public InsertQuery into(String tableName) {
		return this.addFrom(tableName);
	}

	/**
	 * Metoda je ekvivalet metody addFrom(String tableName, String alias).
	 * 
	 * @param tableName - table name
	 * @param alias - alias
	 * @return vraci ukazatel na instanci InsertQuery
	 */
	public InsertQuery into(String tableName, String alias) {
		return this.addFrom(tableName, alias);
	}
	
	/**
	 * Metoda je ekvivalet metody addFrom(UniQLFrom from).
	 * 
	 * @param from - from
	 * @return vraci ukazatel na instanci InsertQuery
	 */
	public InsertQuery into(UniQLFrom from) {
		return this.addFrom(from);
	}

	// -----  -----------------------------------------------------------------

	/**
	 * Navraci hodnotu tables.
	 * 
	 * @return - vraci hodnotu tables
	 */
	public ArrayList<UniQLExpr> getTables() {
		if (tables == null) {
			tables = new ArrayList<UniQLExpr>();
		}
		return tables;
	}

	/**
	 * Navraci hodnotu keys.
	 * 
	 * @return - vraci hodnotu keys
	 */
	public ArrayList<Object> getKeys() {
		return keys;
	}

	/**
	 * Navraci hodnotu values.
	 * 
	 * @return - vraci hodnotu values
	 */
	public ArrayList<Object> getValues() {
		return values;
	}

	/**
	 * Navraci hodnotu params.
	 * 
	 * @return - vraci hodnotu params
	 */
	public Map<Object, Object> getParams() {
		return params;
	}

	// ----- -----------------------------------------------------------------

	/*
	 * (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#transToXML()
	 */
	public String transToXML() throws Exception {
		DOMElement node = new DOMElement("InsertQuery");
		transToXML(node);
		return node.asXML();
	}

	/*
	 * (non-Javadoc)
	 * @see cz.acies.uniql.sql.UniQLExpr#transToXML(org.dom4j.dom.DOMElement)
	 */
	public DOMElement transToXML(DOMElement node) throws Exception {
		node.setName("InsertQuery");
		if (this.getTables() != null) {
			DOMElement child = new DOMElement("tables");
			for (Iterator<UniQLExpr> i = getTables().iterator(); i.hasNext();) {
				UniQLExpr table = i.next();
				child.appendChild(table.transToXML(new DOMElement("UniQLExpr")));
			}
			node.appendChild(child);
		}
		if (keys.size() > 0) {
			DOMElement child = new DOMElement("keys");
			int cnt = 0;
			for (Iterator<Object> i = keys.iterator(); i.hasNext();) {
				Object obj = i.next();
				if (obj instanceof String) {
					UniQLColumn expr = new UniQLColumn(getDialect());
					expr.setName((String)obj);
					child.appendChild(expr.transToXML(new DOMElement("UniQLExpr")));
				}
				cnt++;
			}
			node.appendChild(child);
		}
		if (values.size() > 0) {
			DOMElement child = new DOMElement("values");
			int cnt = 0;
			for (Iterator<Object> i = keys.iterator(); i.hasNext();) {
				Object obj = i.next();
				if (obj instanceof UniQLExpr) {
					//UniQLExpr expr = (UniQLExpr)obj;	
				} else {
					Element valnode = child.addElement("VAL"+cnt);
					valnode.addText("" + obj);
				}
			}
			node.appendChild(child);
		}
		return node;
	}
}
